package top.majuan.study.study.dao;

import top.majuan.study.study.entity.TbNotice;
import top.majuan.study.study.entity.Tbfootball;
import top.majuan.study.study.servlet.user.RegServlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbfootballDAO {
    public static final String INSERT = "insert into tb_football_club(tname,league,gnames,champions,intro,cups,fans,goals) values(?,?,?,?,?,?,?,?)";
    public static final String QUERY = "select id,tname,league,intro,champions,cups,fans,goals,gnames,create_time from tb_football_club order by id";
    public static final String QUERY_BY_ID = "select * from tb_football_club where id = ?";
    public static final String DELETE = "delete from tb_football_club where id = ?";
    public static final String UPDATE = "update tb_football_club set tname = ?,league = ?,champions = ?,cups = ?,fans = ?,goals = ?,gnames = ? where id=?";

    public static int insert(Tbfootball football)throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, football.getTname());
        ps.setString(2, football.getLeague());
        ps.setString(3, football.getGnames());
        ps.setInt(4, football.getChampions());
        ps.setString(5, football.getIntro());
        ps.setInt(6, football.getCups());
        ps.setInt(7, football.getFans());
        ps.setInt(8, football.getGoals());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public  static List<Tbfootball> query() throws Exception {
        List<Tbfootball> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Tbfootball football = new Tbfootball();
            football.setId(rs.getInt("id"));
            football.setTname(rs.getString("tname"));
            football.setLeague(rs.getString("league"));
            football.setIntro(rs.getString("intro"));
            football.setChampions(rs.getInt("champions"));
            football.setCups(rs.getInt("cups"));
            football.setFans(rs.getInt("fans"));
            football.setGoals(rs.getInt("goals"));
            football.setGnames(rs.getString("gnames"));
            football.setCreate_time(rs.getDate("create_time"));
            list.add(football);
        }
        return list;
    }

    public static Tbfootball queryById(Integer id) throws Exception {
        Tbfootball football = null;
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            football = new Tbfootball();
            football.setId(rs.getInt("id"));
            football.setTname(rs.getString("tname"));
            football.setLeague(rs.getString("league"));
            football.setIntro(rs.getString("intro"));
            football.setChampions(rs.getInt("champions"));
            football.setCups(rs.getInt("cups"));
            football.setFans(rs.getInt("fans"));
            football.setGoals(rs.getInt("goals"));
            football.setGnames(rs.getString("gnames"));
            football.setCreate_time(rs.getDate("create_time"));
        }
        conn.close();
        return football;
        }

        public static int delete(Integer id) throws Exception {
            Connection conn = DBHelper.getConnection();
            PreparedStatement ps = conn.prepareStatement(DELETE);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            conn.close();
            return i;
        }

        public static int update(Tbfootball football) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, football.getTname());
        ps.setString(2, football.getLeague());
        ps.setInt(3, football.getChampions());
        ps.setInt(4, football.getCups());
        ps.setInt(5, football.getFans());
        ps.setInt(6, football.getGoals());
        ps.setString(7, football.getGnames());
        ps.setInt(8, football.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
        }
    }


